using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// SqlServerProvider Class designed by Pham Huu Khang
/// Enable you to connect SQL Server database 
/// with following properties and options
/// </summary>
public partial class SqlServerProvider
{
    #region Private Variables
    private SqlConnection sqlConnection;
    private SqlCommand sqlCommand;

    #endregion

    #region ConnectionString Property
    /// <summary>
    /// ConnectionString include Server Name, Database name
    /// provide UserID and Password if you use SQL Server Authentication
    /// otherwise you can use Integrated Security = True or SSPI
    /// </summary>
    private string connectionString = ""; 
    public string ConnectionString
    {
        set { connectionString = value; }
        get { return connectionString; }
    }

    #endregion

    #region CommandType and CommandText
    private string commandText = "";
    public string CommandText
    {
        set { commandText = value; }
        get{return commandText;}
    }
    
    private CommandType commandType
    = CommandType.Text;
    public CommandType CommandType
    {
        set { commandType = value; }
        get { return commandType; }
    }
    #endregion

    #region Parameter Collection
    private string[] parameterCollection = null;
    public string[] ParameterCollection
    {
        set { parameterCollection = value; }
        get { return parameterCollection; }
    }

    private string[] valueCollection;
    public string[] ValueCollection
    {
        set { valueCollection = value; }
        get { return valueCollection; }
    }
    #endregion

    #region Parameter for Return Value
    private string returnValueParameter = "";
    public string ReturnValueParameter
    {
        set { returnValueParameter = value; }
        get { return returnValueParameter; }
    }

    private int returnValue = 0;
    public int ReturnValue
    {       
        get { return returnValue; }
    }
    #endregion

    #region Error Information
    private string errorMessage = "";
    public string ErrorMessage
    {         
        get { return errorMessage; }
    }
    private int errorCode = 0;
    public int ErrorCode
    {         
        get { return errorCode; }
    }
    #endregion

    #region Constructor
    public SqlServerProvider()
    {
        connectionString = "server=(local);" +
            "database=RecruitVietnam;" +
            "Integrated Security=SSPI;";
        OpenConnection();    
    }
    public SqlServerProvider(string conString)
    {
        connectionString = conString;
        OpenConnection();
    }
    public SqlServerProvider(string serverName, string databaseName)
    {
        connectionString = "server=" + serverName + ";" +
            "database=" + databaseName + ";" +
            "Integrated Security=SSPI;";
        OpenConnection();
    }
    public SqlServerProvider(string serverName, string databaseName,
    string userName, string passWord)
    {
        
        connectionString = "server=" + serverName + ";" +
            "database=" + databaseName + ";" +
            "UserID=" + userName + ";"+
            "Password=" + passWord + ";";
        OpenConnection();
    }
    #endregion

    #region Open and Close Connection
    private void OpenConnection()
    {
        if (sqlConnection == null || 
        sqlConnection.State == ConnectionState.Closed)
        {
            sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = connectionString;
            sqlConnection.Open();
        }
    }
     
    public void CloseConnection()
    {
        
        if (sqlConnection.State != ConnectionState.Closed)
        {
             sqlConnection.Close();
        }
        sqlConnection.Dispose();
    }
    #endregion

    #region Add Parameters
    private void AddParameters()
    {
        for (int i = 0; 
        i < parameterCollection.Length; i++)
        {
            sqlCommand.Parameters.AddWithValue(
            parameterCollection[i], valueCollection[i]);
        }
    }
    #endregion
     
     #region Init Parameters
     private void InitParameters()
     {
         sqlCommand = new SqlCommand();
         sqlCommand.CommandText = commandText;
         sqlCommand.Connection = sqlConnection;
         sqlCommand.CommandType = commandType;
         if (ParameterCollection != null && ParameterCollection.Length > 0)
             AddParameters();

         if (returnValueParameter != "")
         {
             SqlParameter sqlParameter = new SqlParameter();
             sqlParameter.ParameterName = returnValueParameter;
             sqlParameter.Direction = ParameterDirection.ReturnValue;
             sqlCommand.Parameters.Add(sqlParameter);
         }
     }
     #endregion
}
 